package stores;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import workspace.Workspace;
import beans.Tipus;

public class TipusStoreImpl implements TipusStore {

	@Override
	public List<Tipus> loadTipusLista(Workspace workspace) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Tipus> tipusLista = new LinkedList<Tipus>();
		Tipus tipus = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, TIPUSNEV FROM TIPUS WHERE AKTIV = 1");
			rs = ps.executeQuery();
			while (rs.next()) {
				tipus = buildTipus(rs);
				tipusLista.add(tipus);
			}
			return tipusLista;
		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Tipus loadTipusByName(Workspace workspace, String tipusnev)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Tipus tipus = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, TIPUSNEV FROM TIPUS WHERE AKTIV = 1 AND TIPUSNEV = ?");
			int i = 1;
			ps.setString(i, tipusnev);
			rs = ps.executeQuery();
			while (rs.next()) {
				tipus = buildTipus(rs);
			}
			return tipus;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Tipus loadTipusById(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Tipus tipus = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, TIPUSNEV FROM TIPUS WHERE AKTIV = 1 AND ID = ?");
			int i = 1;
			ps.setLong(i++, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				tipus = buildTipus(rs);
			}
			return tipus;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Tipus insertTipus(Workspace workspace, Tipus ujTipus)
			throws SQLException {
		PreparedStatement ps = null;
		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("INSERT INTO TIPUS(WRITER, TIPUSNEV, AKTIV) VALUES (?, ?, 1)");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setString(i++, ujTipus.getTipusNev());
			int sorValtNum = ps.executeUpdate();
			if (sorValtNum == 1) {
				workspace.commit();
				ujTipus.setId(getGeneratedKey(workspace));
				return ujTipus;
			} else {
				return null;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException();
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public Tipus updateTipus(Workspace workspace, Tipus updatedTipus)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("UPDATE TIPUS SET WRITER = ?, TIPUSNEV = ? WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setString(i++, updatedTipus.getTipusNev());
			ps.setLong(i++, updatedTipus.getId());
			int sorValtNum = ps.executeUpdate();
			if (sorValtNum == 1) {
				workspace.commit();
				return loadTipusById(workspace, updatedTipus.getId());
			} else {
				return null;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public boolean deleteTipus(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("UPDATE TIPUS SET WRITER = ?, AKTIV = 0 WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setLong(i++, id);
			int sorValtNum = ps.executeUpdate();
			if (sorValtNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public boolean deleteTipusForTest(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace
					.getPreparedStatement("DELETE FROM TIPUS WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, id);
			int sorValtNum = ps.executeUpdate();
			if (sorValtNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	private long getGeneratedKey(Workspace workspace) throws SQLException {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = workspace
					.getPreparedStatement("SELECT IDENT_CURRENT('TIPUS')");
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getLong(1);
			} else
				return -1L;
		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	private Tipus buildTipus(ResultSet rs) throws SQLException {

		int i = 1;
		Tipus newTipus = new Tipus();
		newTipus.setId(rs.getLong(i++));
		newTipus.setTipusNev(rs.getString(i++));
		return newTipus;
	}

}
